package trastienda.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import trastienda.excepcion.DAOExcepcion;
import trastienda.modelo.Instrumentos;
import trastienda.modelo.Servicio;
import trastienda.modelo.reservas;
import trastienda.util.ConexionBD;
public class reservasDAO extends BaseDAO {
	
	public reservas insertar(reservas ins) throws DAOExcepcion {

		
		String query = "INSERT INTO reservas( fecha ,hora, precioTotal,idCliente,idArmado) VALUES (?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, ins.getfecha());
			stmt.setString(2, ins.gethora());
			stmt.setDouble(3, ins.getprecioTotal());
			stmt.setString(4, ins.getidCliente());
			stmt.setInt(5, ins.getidArmado());
			
			int i = stmt.executeUpdate();
			
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			
					  // Obtener el ultimo id
						int dato1 = 0;
						query = "select last_insert_id()";
						stmt = con.prepareStatement(query);
						rs = stmt.executeQuery();
						if (rs.next()) {
						dato1 = rs.getInt(1);}
			
					
					
					Collection<Servicio> servicios = ins.getServicio();
					
					for (Servicio sss : servicios) {
						
						query = "INSERT INTO reservas_has_servicios(idReserva, idServicios) VALUES (?,?)";
						
						
						stmt = con.prepareStatement(query);
						stmt.setDouble(1, dato1);
						stmt.setInt(2, sss.getIdServicios());
					
						i = stmt.executeUpdate();
						if (i != 1) {
							throw new SQLException("No se pudo insertar");
						}
						
					}
					
					// Obtener el ultimo id
					int	dato2 = 0;
						query = "select last_insert_id()";
						stmt = con.prepareStatement(query);
						rs = stmt.executeQuery();
						if (rs.next()) {
						dato2 = rs.getInt(1);}
					
					
					Collection<Instrumentos> instrumentos = ins.getInstrumentos();
					
					for (Instrumentos instmto : instrumentos) {
						

						query = "INSERT INTO Instrumentos_has_Reservas(idReserva,idInstrumento) VALUES (?,?)";
						
						
						stmt = con.prepareStatement(query);
						stmt.setDouble(1, dato2);
						stmt.setInt(2, instmto.getidInstrumento());
					
						i = stmt.executeUpdate();
						if (i != 1) {
							throw new SQLException("No se pudo insertar");
						}
					}
					
					
						
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return ins;
	} // Fin de Insertar
		
	public reservas obtener(int idReserva) throws DAOExcepcion {
		reservas ins = new reservas();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			String query = "SELECT * FROM reservas WHERE idReserva=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idReserva);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				ins.setidReserva(rs.getInt(1));
				ins.setfecha(rs.getString(2));
				ins.sethora(rs.getString(3));
				ins.setprecioTotal(rs.getDouble(4));
				ins.setidCliente(rs.getString(5));
				ins.setidArmado(rs.getInt(6));
		
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return ins;
	}// Fin de Obtener

	public Collection<reservas> buscar(String fecha, String hora)
		
		throws DAOExcepcion {
		String query = "SELECT * FROM reservas WHERE (hora LIKE ? AND fecha LIKE ?)";
	
		Collection<reservas> lista = new ArrayList<reservas>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + fecha + "%");
			stmt.setString(2,"%" + hora + "%");
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				reservas vo = new reservas();
				vo.setidReserva(rs.getInt("idReserva"));
				vo.setfecha(rs.getString("fecha"));
				vo.sethora(rs.getString("hora"));
				vo.setprecioTotal(rs.getDouble("precioTotal"));
				vo.setidCliente(rs.getString("idCliente"));
				vo.setidCliente(rs.getString("idCliente"));	
				vo.setidArmado(rs.getInt("idArmado"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	public Collection<reservas> listar() throws DAOExcepcion {
		Collection<reservas> c = new ArrayList<reservas>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT * FROM reservas ORDER BY fecha";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				reservas vo = new reservas();
				vo.setidReserva(rs.getInt("idReserva"));
				vo.setfecha(rs.getString("fecha"));
				vo.sethora(rs.getString("hora"));
				vo.setprecioTotal(rs.getDouble("precioTotal"));
				vo.setidCliente(rs.getString("idCliente"));
				vo.setidCliente(rs.getString("idCliente"));	
				vo.setidArmado(rs.getInt("idArmado"));
				c.add(vo);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

}